IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Oee_Eqp_State_Event]') AND type in (N'U'))
DROP TABLE [dbo].[Oee_Eqp_State_Event];
CREATE TABLE [dbo].[Oee_Eqp_State_Event](
    Sys_Id bigint NOT NULL IDENTITY(1,1),
    Eqp_Id VARCHAR(50) NOT NULL,
    Plc_Value INT NOT NULL,
    State VARCHAR(30) NOT NULL,
    CREATED_TIME DATETIME,
    PRIMARY KEY (Sys_Id)
)

EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Event, null, null;
EXEC sp_addextendedproperty 'MS_Description', 'PK', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Event, 'column', Sys_Id;
EXEC sp_addextendedproperty 'MS_Description', 'EqpID', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Event, 'column', Eqp_Id;
EXEC sp_addextendedproperty 'MS_Description', 'Plc状态数值', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Event, 'column', Plc_Value;
EXEC sp_addextendedproperty 'MS_Description', '状态字符串', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Event, 'column', State;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Event, 'column', CREATED_TIME;

 
CREATE INDEX IDX_Eqp_State_Event_CTIME ON Oee_Eqp_State_Event(CREATED_TIME);
CREATE INDEX IDX_Eqp_State_Event_EID ON Oee_Eqp_State_Event(Eqp_Id);



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Oee_Eqp]') AND type in (N'U'))
DROP TABLE [dbo].[Oee_Eqp];
CREATE TABLE [dbo].[Oee_Eqp](
    Eqp_Id VARCHAR(50) NOT NULL,
    workshop_Id VARCHAR(50) NOT NULL,
    Eqp_Description VARCHAR(255),
    Vendor VARCHAR(50),
    Eqp_Type VARCHAR(50),
    CREATED_BY VARCHAR(50),
    CREATED_TIME DATETIME,
    UPDATED_BY VARCHAR(50),
    UPDATED_TIME DATETIME,
    PRIMARY KEY (Eqp_Id)
)

EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', Oee_Eqp, null, null;
EXEC sp_addextendedproperty 'MS_Description', '机台Id', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', Eqp_Id;
EXEC sp_addextendedproperty 'MS_Description', '车间Id', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', workshop_Id;
EXEC sp_addextendedproperty 'MS_Description', '机台描述', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', Eqp_Description;
EXEC sp_addextendedproperty 'MS_Description', '厂商', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', Vendor;
EXEC sp_addextendedproperty 'MS_Description', '机台类型', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', Eqp_Type;
EXEC sp_addextendedproperty 'MS_Description', '创建人', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', CREATED_BY;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', CREATED_TIME;
EXEC sp_addextendedproperty 'MS_Description', '更新人', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', UPDATED_BY;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', Oee_Eqp, 'column', UPDATED_TIME;


CREATE INDEX IDX_Eqp_WORKSHOP_ID ON Oee_Eqp(workshop_Id);


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Oee_User]') AND type in (N'U'))
DROP TABLE [dbo].[Oee_User];
CREATE TABLE [dbo].[Oee_User](
    User_Id VARCHAR(50) NOT NULL,
    User_Pwd VARCHAR(50) NOT NULL,
    CREATED_BY VARCHAR(50),
    CREATED_TIME DATETIME,
    UPDATED_BY VARCHAR(50),
    UPDATED_TIME DATETIME,
    PRIMARY KEY (User_Id)
)

EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', Oee_User, null, null;
EXEC sp_addextendedproperty 'MS_Description', '用户名', 'SCHEMA', dbo, 'table', Oee_User, 'column', User_Id;
EXEC sp_addextendedproperty 'MS_Description', '密码', 'SCHEMA', dbo, 'table', Oee_User, 'column', User_Pwd;
EXEC sp_addextendedproperty 'MS_Description', '创建人', 'SCHEMA', dbo, 'table', Oee_User, 'column', CREATED_BY;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', Oee_User, 'column', CREATED_TIME;
EXEC sp_addextendedproperty 'MS_Description', '更新人', 'SCHEMA', dbo, 'table', Oee_User, 'column', UPDATED_BY;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', Oee_User, 'column', UPDATED_TIME;


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Oee_workshop]') AND type in (N'U'))
DROP TABLE [dbo].[Oee_workshop];
CREATE TABLE [dbo].[Oee_workshop](
    Workshop_id VARCHAR(50) NOT NULL,
    Workshop_Description VARCHAR(50) NOT NULL,
    Trigger_Alarm_Threshold INT,
    Alarm_PC_Hostname VARCHAR(255),
    CREATED_BY VARCHAR(32),
    CREATED_TIME DATETIME,
    UPDATED_BY VARCHAR(32),
    UPDATED_TIME DATETIME,
    PRIMARY KEY (Workshop_id)
)

EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', Oee_workshop, null, null;
EXEC sp_addextendedproperty 'MS_Description', 'Workshop_id', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', Workshop_id;
EXEC sp_addextendedproperty 'MS_Description', '车间描述', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', Workshop_Description;
EXEC sp_addextendedproperty 'MS_Description', '允许最大空闲期(分钟)', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', Trigger_Alarm_Threshold;
EXEC sp_addextendedproperty 'MS_Description', '告警弹窗PC名', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', Alarm_PC_Hostname;
EXEC sp_addextendedproperty 'MS_Description', '创建人', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', CREATED_BY;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', CREATED_TIME;
EXEC sp_addextendedproperty 'MS_Description', '更新人', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', UPDATED_BY;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', Oee_workshop, 'column', UPDATED_TIME;


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Oee_Eqp_State_Result]') AND type in (N'U'))
DROP TABLE [dbo].[Oee_Eqp_State_Result];
CREATE TABLE [dbo].[Oee_Eqp_State_Result](
    Sys_Id bigint NOT NULL,
    Seq int NOT NULL,
    Eqp_Id VARCHAR(50) NOT NULL,
    Old_State VARCHAR(30) NOT NULL,
    Old_State_Duration INT,
    Old_State_Create_Time DATETIME,
    Old_State_End_Time DATETIME,
    New_Event_Sys_Id bigint,
    New_State VARCHAR(30),
    Created_Time DATETIME,
    PRIMARY KEY (Sys_Id,Seq)
)

EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, null, null;
EXEC sp_addextendedproperty 'MS_Description', '老event  sysId', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Sys_Id;
EXEC sp_addextendedproperty 'MS_Description', '老Event拆分后的份数序号', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Seq;
EXEC sp_addextendedproperty 'MS_Description', 'EqpID', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Eqp_Id;
EXEC sp_addextendedproperty 'MS_Description', '老状态字符串', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Old_State;
EXEC sp_addextendedproperty 'MS_Description', '老状态持续秒数', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Old_State_Duration;
EXEC sp_addextendedproperty 'MS_Description', '老状态开始时间', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Old_State_Create_Time;
EXEC sp_addextendedproperty 'MS_Description', '老状态结束时间', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Old_State_End_Time;
EXEC sp_addextendedproperty 'MS_Description', '新event  sysId', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', New_Event_Sys_Id;
EXEC sp_addextendedproperty 'MS_Description', '新状态字符串', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', New_State;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', Oee_Eqp_State_Result, 'column', Created_Time;



 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OEE_Hour_Dimension]') AND type in (N'U'))
DROP TABLE [dbo].[OEE_Hour_Dimension];
CREATE TABLE [dbo].[OEE_Hour_Dimension](
    Hour_Id varchar(13) NOT NULL,
    Date_Id varchar(10) NOT NULL,
    CREATED_TIME DATETIME DEFAULT  getdate(),
    PRIMARY KEY (Hour_Id)
)

EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', OEE_Hour_Dimension, null, null;
EXEC sp_addextendedproperty 'MS_Description', 'PK', 'SCHEMA', dbo, 'table', OEE_Hour_Dimension, 'column', Hour_Id;
EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', OEE_Hour_Dimension, 'column', Date_Id;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', OEE_Hour_Dimension, 'column', CREATED_TIME;




---- 增加一个OEE系统账号 
delete from Oee_User ;
insert into Oee_User (user_Id, User_Pwd) values ('SuperAdmin','')
